package cj.web.kly.dao;
/*
 *  
 *  
*/
import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.kly.domain.News;
import cj.web.kly.domain.SubjectNews;

/**
 * 
 * @author 
 *
 */
@DAO
public interface SubjectNewsDAO{
	
	@SQL("SELECT a.id,a.type,a.columnsId,a.subjectId,a.subjectColumnsId,a.title,a.abstractInfor,a.carouselImage,a.source,a.author,a.status,a.releaseState,a.releaseAt,a.isTop,a.stickAt,a.pvNum,a.pvNumFinal,b.name as columnsName,c.descs as subjectColumnsName FROM SubjectNews a left join Columns b on a.columnsId=b.id left join SubjectColumns c on a.subjectColumnsId=c.id WHERE a.deleteFlag=1  #if(:t.title != null){ AND (a.title LIKE '%##(:t.title)%' or a.content LIKE '%##(:t.content)%') } ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<SubjectNews> queryBB(@SQLParam("t") SubjectNews news);
		
	@SQL("SELECT count(1) FROM SubjectNews WHERE deleteFlag=1 #if(:t.title != null){ AND (title LIKE '%##(:t.title)%' or content LIKE '%##(:t.content)%') } " )
    int queryBBSize(@SQLParam("t") SubjectNews news);
	
	@SQL("SELECT a.id,a.type,a.columnsId,a.subjectId,a.subjectColumnsId,a.title,a.abstractInfor,a.carouselImage,a.source,a.author,a.status,a.releaseState,a.releaseAt,a.isTop,a.stickAt,a.pvNum,a.pvNumFinal,b.name as columnsName,b.id as treeIdIn FROM News a join Columns b on a.columnsId=b.id WHERE a.deleteFlag=1 and a.columnsId is not null #if(:t.treeIdIn != null){ AND a.columnsId in (##(:t.treeIdIn)) } #if(:t.title != null){ AND (a.title LIKE '%##(:t.title)%' or a.content LIKE '%##(:t.content)%') } ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<News> query(@SQLParam("t") SubjectNews news);
		
	@SQL("SELECT count(1) FROM News WHERE deleteFlag=1 and columnsId is not null #if(:t.treeIdIn != null){ AND columnsId in (##(:t.treeIdIn)) } #if(:t.title != null){ AND (title LIKE '%##(:t.title)%' or content LIKE '%##(:t.content)%') } " )
    int querySize(@SQLParam("t") SubjectNews news);
    
    @SQL("SELECT a.*,b.name as columnsName,b.id as treeIdIn FROM SubjectNews a join Columns b on a.columnsId=b.id WHERE a.id=:1 ")
    SubjectNews queryById(int id);
    
    @SQL("INSERT INTO SubjectNews (type,columnsId,subjectId,subjectColumnsId,title,abstractInfor,carouselImage,content,source,author,status,releaseState,releaseAt,isTop,stickAt,pvNum,pvNumFinal,deleteFlag,createUser,createAt,updateUser,updateAt) VALUES (:t.type,:t.columnsId,:t.subjectId,:t.subjectColumnsId,:t.title,:t.abstractInfor,:t.carouselImage,:t.content,:t.source,:t.author,:t.status,:t.releaseState,:t.releaseAt,:t.isTop,:t.stickAt,0,0,1,:t.createUser,now(),:t.updateUser,now()) ")
    void create(@SQLParam("t") SubjectNews news);
    
    @SQL("INSERT INTO SubjectNews (type,columnsId,subjectId,subjectColumnsId,title,abstractInfor,carouselImage,content,source,author,status,releaseState,releaseAt,isTop,stickAt,pvNum,pvNumFinal,deleteFlag,createUser,createAt,updateUser,updateAt)"
    		+ " SELECT type,columnsId,:2,:3,title,abstractInfor,carouselImage,content,source,author,status,releaseState,releaseAt,0,null,0,0,1,createUser,createAt,updateUser,updateAt FROM News "
    		+ " WHERE deleteFlag =1 AND id IN (##(:1)) ")
    void createInsertInto(String Ids,int subjectId,int subjectColumnsId);
	
	@SQL("UPDATE SubjectNews SET "
			        + "type=:t.type,"
			        + "columnsId=:t.columnsId,"
			        + "subjectId=:t.subjectId,"
			        + "subjectColumnsId=:t.subjectColumnsId,"
			        + "title=:t.title,"
			        + "abstractInfor=:t.abstractInfor,"
			        + "carouselImage=:t.carouselImage,"
			        + "content=:t.content,"
			        + "source=:t.source,"
			        + "author=:t.author,"
			        + "status=:t.status,"
			        + "releaseAt=:t.releaseAt,"
			        + "isTop=:t.isTop,"
			        + "updateUser=:t.updateUser,"
			        + "updateAt=now()"
					+ " WHERE id=:t.id ")
    void update(@SQLParam("t") SubjectNews news);
    
    @SQL("UPDATE SubjectNews SET deleteFlag=0 WHERE id=:1 ")
    void deleteById(int id);
    
    @SQL("UPDATE SubjectNews SET stickAt=now(),updateAt=now() WHERE id=:1 ")
	void updateByStickTime(int id);

    @SQL("UPDATE SubjectNews SET stickAt=null,updateAt=now() WHERE id=:1 ")
	void updateByStickTimeIsNull(int id);

    @SQL("UPDATE SubjectNews SET pvNum=:2,updateAt=now() WHERE id=:1 ")
	void updatePV(int id,int pvNum);
    
    @SQL("UPDATE SubjectNews SET releaseState=:2,updateAt=now() WHERE id=:1 ")
	void updateReleaseStateById(int id,int releaseState);
    
    @SQL("SELECT a.id,a.type,a.columnsId,a.subjectId,a.subjectColumnsId,a.title,a.abstractInfor,a.carouselImage,a.source,a.author,a.status,a.releaseState,a.releaseAt,a.isTop,a.stickAt,a.pvNum,a.pvNumFinal,b.name as subjectName,b.id as treeIdIn,c.name as subjectColumnsName FROM SubjectNews a join Subject b on a.subjectId=b.id join SubjectColumns c on a.subjectColumnsId=c.id WHERE a.deleteFlag=1 and a.subjectId is not null #if(:t.subjectId != null){ AND a.subjectId=:t.subjectId } #if(:t.title != null){ AND a.title LIKE '%##(:t.title)%' AND a.content LIKE '%##(:t.content)%' } ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<SubjectNews> querySubject(@SQLParam("t") SubjectNews news);
		
	@SQL("SELECT count(1) FROM SubjectNews WHERE deleteFlag=1 and subjectId is not null #if(:t.subjectId != null){ AND subjectId=:t.subjectId } #if(:t.title != null){ AND title LIKE '%##(:t.title)%' AND content LIKE '%##(:t.content)%' } " )
    int querySubjectSize(@SQLParam("t") SubjectNews news);
    
    @SQL("SELECT a.*,b.name as subjectName,b.id as treeIdIn,c.name as subjectColumnsName FROM SubjectNews a join Subject b on a.subjectId=b.id join SubjectColumns c on a.subjectColumnsId=c.id  WHERE a.id=:1 ")
    SubjectNews querySubjectById(int id);
    
    @SQL("UPDATE SubjectNews SET isTop=1,updateAt=now() WHERE id=:1 ")
	void updateByIsTop(int id);

    @SQL("UPDATE SubjectNews SET isTop=0,updateAt=now() WHERE id=:1 ")
	void updateByIsNotTop(int id);
}